using System;
using System.Collections;
using System.Collections.Generic;
using System.Linq;
using Core;
using IglooCoder.Commons.WcfNhibernate;
using NHibernate;
using NHibernate.Criterion;
using NHibernate.Linq;
using NHibernate.SqlCommand;

namespace Infrastucture
{
    public class PurchaseOrderRepository : BaseNHibernateRepository<PurchaseOrder>
    {
        public PurchaseOrderRepository(ISessionStorage sessionStorage)
            : base(sessionStorage)
        {
        }

        public string GetMaxNumber(string kodeAwal)
        {
            string res = string.Empty;
            var lenKode = kodeAwal.Length + 10;
            var query = Session.CreateSQLQuery("Select Max(PONumber) as Number from PurchaseOrder  where PONumber like'" + kodeAwal + "%' AND  Len(PONumber)=" + lenKode).AddScalar("Number", NHibernateUtil.String);
            var objRes = query.List()[0];
            if (objRes != null) res = objRes.ToString();
            return res;
        }


        public void DeleteDetail(PurchaseOrderDetail purchaseOrderDetail)
        {
            using (ITransaction tx = Session.BeginTransaction())
            {
                var POdetail = Session.Get<PurchaseOrderDetail>(purchaseOrderDetail.PurchaseOrderDetailId);
                Session.Delete(POdetail);
                tx.Commit();
            }
        }

       public void UpdateStatus(string PONumber, int Status)
       {
           ISQLQuery command =
           Session.CreateSQLQuery("Update PurchaseOrder set Status=" + Status + " Where PoNumber='" + PONumber + "'");
           int res = command.ExecuteUpdate();
       }

       public IList<PurchaseOrder> GetDataPaged(int page, int pageSize, string sortProperty = "", string sortType = "")
       {
           var Results = new List<PurchaseOrder>();

          
           using (ITransaction tx = Session.BeginTransaction())
           {
               ICriteria criteria = Session.CreateCriteria<PurchaseOrder>()
                   .CreateAlias("Supplier", "sp",JoinType.InnerJoin)
                   .CreateAlias("Warehouse", "w",JoinType.LeftOuterJoin)
                   .CreateAlias("Currency", "c",JoinType.LeftOuterJoin)
                   .CreateAlias("Btbs", "btbs",JoinType.LeftOuterJoin);
               if (sortType != "")
               {
                   if (sortType.ToUpper() == "ASC")
                       criteria.AddOrder(new Order(sortProperty, true));
                   else
                       criteria.AddOrder(new Order(sortProperty, false));
               }

               criteria.SetFirstResult((page - 1) * pageSize).SetMaxResults(pageSize);

               ICriteria criteriacount =
                   Session.CreateCriteria<PurchaseOrder>()
                   .SetProjection(Projections.RowCountInt64());

               IList Res = Session.CreateMultiCriteria().Add(criteria).Add(criteriacount).List();

               foreach (object o in (IList)Res[0])
               {
                   var po = (PurchaseOrder) o;
                   Results.Add(po);
               }

               RecordCount = (long)((IList)Res[1])[0];
               tx.Commit();
           }

           return Results;
       }


       public override IList<PurchaseOrder>  GetDataPaged(Dictionary<string,object> Criteria, string expression, int page, int pageSize)
       {
           var Results = new List<PurchaseOrder>();


           using (ITransaction tx = Session.BeginTransaction())
           {
               ICriteria criteria = Session.CreateCriteria<PurchaseOrder>()
                   .CreateAlias("Supplier", "sp", JoinType.InnerJoin)
                   .CreateAlias("Warehouse", "w", JoinType.LeftOuterJoin)
                   .CreateAlias("Currency", "c", JoinType.LeftOuterJoin)
                   .CreateAlias("Btbs", "btbs", JoinType.LeftOuterJoin);
               criteria.SetFirstResult((page - 1) * pageSize).SetMaxResults(pageSize);

               var filter = new List<string>();
               int i = 0;
               foreach (var criterion in Criteria)
                   if (!typeof(PurchaseOrder).GetProperties().Any(
                           x => x.Name == criterion.Key.Split(new[] { "." }, StringSplitOptions.None)[0]))
                       filter.Add(criterion.Key);

               filter.ForEach((x) => { Criteria.Remove(x); });

               dynamic exp = null;
               if (expression == "AND")
                   exp = Expression.Conjunction();

               else
                   exp = Expression.Disjunction();

               foreach (var criterion in Criteria)
               {
                   if (criterion.Value == null) continue;
                   if (criterion.Value.GetType() == typeof (string))
                       exp.Add(Expression.Like(criterion.Key, criterion.Value.ToString(), MatchMode.Anywhere));
                   else
                       exp.Add(Expression.Eq(criterion.Key, criterion.Value));
                   i += 1;
               }
               criteria.Add(exp);
               ICriteria criteriacount =
                   Session.CreateCriteria<PurchaseOrder>()
                   .SetProjection(Projections.RowCountInt64());

               IList Res = Session.CreateMultiCriteria().Add(criteria).Add(criteriacount.Add(exp)).List();

               foreach (object o in (IList)Res[0])
               {
                   var po = (PurchaseOrder)o;
                   Results.Add(po);
               }

               RecordCount = (long)((IList)Res[1])[0];
               tx.Commit();
           }

           return Results;
       }

       public void UpdateEmailStatus(string poNo)
       {
           ISQLQuery command =
           Session.CreateSQLQuery("Update PurchaseOrder set HasSent=1 Where PoNumber='" + poNo + "'");
           int res = command.ExecuteUpdate();
       }
    }
}